import sqlite3
from PyQt5 import QtWidgets
from PyQt5.QtWidgets import QTableWidgetItem, QMenu, QAction
from PyQt5.QtCore import Qt, QPoint
import datetime
from menu_utils import request_list, mark_ip_red, mark_ip_green, aggregate_by_url, aggregate_by_time, all_info

def setup_context_menu(ui, aggregated):
    """设置表格右键菜单"""
    # 创建右键菜单
    if hasattr(ui, 'context_menu'):
        # 断开之前的信号连接
        try:
            ui.tableWidget.customContextMenuRequested.disconnect()
        except TypeError:
            pass  # 没有连接时忽略错误
        
        # 清除菜单项
        ui.context_menu.clear()
    else:
        # 创建新菜单
        ui.context_menu = QMenu(ui.tableWidget)

    # 根据聚合模式创建不同的菜单项
    if aggregated:
        # IP聚合模式下的菜单项
        ui.action_request_list = QAction("请求列表", ui.tableWidget)
        ui.action_ip_red = QAction("IP标红", ui.tableWidget)
        ui.action_ip_green = QAction("IP标绿", ui.tableWidget)
        
        ui.context_menu.addAction(ui.action_request_list)
        ui.context_menu.addAction(ui.action_ip_red)
        ui.context_menu.addAction(ui.action_ip_green)
        
        # 连接信号槽
        ui.action_request_list.triggered.connect(lambda: on_action_triggered(ui, "request_list"))
        ui.action_ip_red.triggered.connect(lambda: on_action_triggered(ui, "ip_red"))
        ui.action_ip_green.triggered.connect(lambda: on_action_triggered(ui, "ip_green"))
    else:
        # 普通模式下的菜单项
        ui.action_all_info = QAction("详细信息", ui.tableWidget)
        ui.action_aggregate_ip = QAction("IP聚合", ui.tableWidget)
        ui.action_aggregate_url = QAction("URL聚合", ui.tableWidget)
        ui.action_aggregate_time = QAction("时间聚合", ui.tableWidget)
        
        ui.context_menu.addAction(ui.action_all_info)
        ui.context_menu.addAction(ui.action_aggregate_ip)
        ui.context_menu.addAction(ui.action_aggregate_url)
        ui.context_menu.addAction(ui.action_aggregate_time)
        
        # 连接信号槽
        ui.action_all_info.triggered.connect(lambda: on_action_triggered(ui, "all_info"))
        ui.action_aggregate_ip.triggered.connect(lambda: on_action_triggered(ui, "aggregate_ip"))
        ui.action_aggregate_url.triggered.connect(lambda: on_action_triggered(ui, "aggregate_url"))
        ui.action_aggregate_time.triggered.connect(lambda: on_action_triggered(ui, "aggregate_time"))
    
    # 设置表格的右键菜单策略
    ui.tableWidget.setContextMenuPolicy(Qt.CustomContextMenu)
    ui.tableWidget.customContextMenuRequested.connect(lambda point: show_context_menu(ui, point, aggregated))
    
    # 保存聚合状态
    ui.aggregated_mode = aggregated

def show_context_menu(ui, point, aggregated):
    """显示右键菜单"""
    # 检查是否有选中的单元格
    selected_items = ui.tableWidget.selectedItems()
    if selected_items:
        # 有选中条目，显示菜单
        ui.context_menu.exec_(ui.tableWidget.mapToGlobal(point))

def on_action_triggered(ui, action_type):
    """处理菜单项触发事件"""
    # 初始化窗口实例字典（如果不存在）
    if not hasattr(ui, '_window_instances'):
        ui._window_instances = {}
    
    # 获取选中的IP
    selected_row = ui.tableWidget.currentRow()
    if selected_row >= 0:
        ip = ui.tableWidget.item(selected_row, 0).text() # 第0列是IP
        url = ui.tableWidget.item(selected_row, 2).text() # 第2列是URL
        timerange = ui.tableWidget.item(selected_row, 5).text() # 第4列是时间范围
        id = selected_row+1  # 使用行号作为ID
        
        # 根据操作类型调用相应的函数
        if ui.aggregated_mode:
            if action_type == "request_list":
                # 保存窗口实例，避免被垃圾回收
                ui._window_instances[f"request_list_{ip}"] = request_list(ip)
            elif action_type == "ip_red":
                mark_ip_red(ip)
            elif action_type == "ip_green":
                mark_ip_green(ip)
        else:
            if action_type == "all_info":
                # 全部信息显示（默认模式）
                ui._window_instances[f"all_info_{id}"] = all_info(id)
            elif action_type == "aggregate_ip":
                # 保存窗口实例
                ui._window_instances[f"request_list_{ip}"] = request_list(ip)
            elif action_type == "aggregate_url":
                # 保存窗口实例
                ui._window_instances[f"aggregate_url_{url[:30]}"] = aggregate_by_url(url)
            elif action_type == "aggregate_time":
                # 保存窗口实例
                ui._window_instances[f"aggregate_time_{timerange[:20]}"] = aggregate_by_time(timerange)

def sql2ui(ui, aggregated = False):
    """
    将 SQLite 数据库中 nginx_logs 表的数据更新到 PyQt5 表格中
    
    Args:
        ui: PyQt5 UI 对象，包含表格组件
        aggregated: 是否按IP聚合显示
    """
    # 清空表格内容
    ui.tableWidget.setRowCount(0)
    
    try:
        # 连接数据库
        conn = sqlite3.connect('nginx_logs.db')
        cursor = conn.cursor()
        
        if aggregated: # 是否是IP聚合
            # 根据IP聚合数据：获取每个IP的最高攻击概率、出现次数、最早和最晚时间戳
            cursor.execute("""
                SELECT 
                    ip, 
                    address,
                    MAX(point) as max_point, 
                    MAX(regular_result) as regular_result,
                    COUNT(*) as occurrence_count,
                    MIN(timestamp) as first_timestamp,
                    MAX(timestamp) as last_timestamp
                FROM nginx_logs 
                GROUP BY ip
                ORDER BY max_point DESC
            """)
        else:
            # 查询数据
            cursor.execute("SELECT ip, address, string, point, regular_result, timestamp FROM nginx_logs LIMIT 10000")
        rows = cursor.fetchall()
        conn.close()
        
        # 根据不同模式设置表格列数和表头
        if aggregated:
            ui.tableWidget.setColumnCount(6)
            ui.tableWidget.setHorizontalHeaderLabels(['IP', '归属', '最高攻击概率(%)', '正则表达式判断结果', '出现次数', '最早时间', '最晚时间'])
            gailv = 2
            time_lie = 5
            regular_result_lie = 3
        else:
            ui.tableWidget.setColumnCount(6)
            ui.tableWidget.setHorizontalHeaderLabels(['IP', '归属', 'URL', '攻击概率(%)','正则表达式判断结果', '时间'])
            gailv = 3
            time_lie = 5
            regular_result_lie = 4
        
        # 设置表格行数
        ui.tableWidget.setRowCount(len(rows))
        
        # 填充表格数据
        for row_idx, row_data in enumerate(rows):
            for col_idx, data in enumerate(row_data):
                # item = QTableWidgetItem(str(data))
                # # 设置单元格居中对齐
                # item.setTextAlignment(Qt.AlignCenter)
                # 根据不同列处理数据
                if col_idx == gailv and data:  # 第3列是攻击概率
                    # 将攻击概率转换为百分比格式（乘以100）
                    try:
                        # 转换为百分比，保留适当小数位
                        display_data = f"{float(data) * 100:.2f}%"
                    except (ValueError, TypeError):
                        display_data = str(data)
                # 处理时间戳，转换为年-月-日 时-分-秒格式
                elif col_idx == regular_result_lie: # 第4列是正则表达式判断结果
                    if data == '1':
                        data = "攻击"
                    elif data == '0':
                        data = "正常"
                    else:
                        data = "未判断"
                    display_data = str(data)
                elif col_idx == time_lie and data: # 第5列是时间戳
                    try:
                        # 假设时间戳是整数秒或浮点数
                        timestamp = float(data)
                        dt = datetime.datetime.fromtimestamp(timestamp)
                        display_data = dt.strftime('%Y-%m-%d %H:%M:%S')
                    except (ValueError, TypeError):
                        display_data = str(data)
                else:
                    # 其他列保持原始格式
                    display_data = str(data)
                
                item = QTableWidgetItem(display_data)
                
                # URL列(第2列)左对齐，其他列居中对齐
                if col_idx == 2 and not aggregated:  # 第3列是URL
                    item.setTextAlignment(Qt.AlignLeft | Qt.AlignVCenter)
                else:
                    item.setTextAlignment(Qt.AlignCenter)

                ui.tableWidget.setItem(row_idx, col_idx, item)
        
        # 自适应列宽
        ui.tableWidget.horizontalHeader().setSectionResizeMode(QtWidgets.QHeaderView.Stretch)
        ui.tableWidget.verticalHeader().setVisible(False)
        # 设置右键菜单
        setup_context_menu(ui, aggregated)
        print(f"成功将 {len(rows)} 条记录加载到表格中")
        
    except Exception as e:
        print(f"将数据加载到表格时出错: {e}")
        import traceback
        traceback.print_exc()